package io.renren.service.excel;

import org.apache.commons.io.IOUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import io.renren.utils.CommonStringHandleUtil;
import io.renren.utils.RRException;
import io.renren.utils.excel.ParseExcelDataUtil;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

@Service
public class GeneratorMysqlFromExcelService {
	
	
	public String GeneratorMysqlFromExcel(MultipartFile file,String tableName) throws Exception {
		StringBuffer sb=new StringBuffer();
		sb.append("INSERT INTO ");
		sb.append(tableName);
		sb.append("(");
		InputStream excelInputStream = file.getInputStream();
        Workbook wb = WorkbookFactory.create(excelInputStream);
        String[] parseTableHead = ParseExcelDataUtil.getExcelHead(wb);
        String tableColumnName=CommonStringHandleUtil.stringArrayToStringWithCommaForSql(parseTableHead);
        sb.append(tableColumnName);
        sb.append(") VALUES ");
        List<Map<String, Object>> parseData = ParseExcelDataUtil.parseExcelFile(wb, parseTableHead);     
        //for(Map<String, Object> map:parseData) {
        for(int start=0;start<parseData.size();start++) {
        	Map<String, Object> map=parseData.get(start);
        	StringBuffer valueBuffer=new StringBuffer();
        	valueBuffer.append("(");
        	for(int i=0;i<parseTableHead.length;i++) {  
        		valueBuffer.append("'");
        		String tmp=(String) map.get(parseTableHead[i]);
        		valueBuffer.append(tmp);
        		valueBuffer.append("'");
        		if(i!=parseTableHead.length-1) {
        			valueBuffer.append(",");
        		}
        	}
        	valueBuffer.append(")");
        	if (start!=parseData.size()-1) {
        		valueBuffer.append(",");
        	}
        	sb.append(valueBuffer.toString());        
        }
        sb.append(";\n commit;");
    
		return sb.toString();
		
	}
	
	public byte[] GeneratorMysqlFromExcelZip(String coments,String fileName){
		ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
		ZipOutputStream zip = new ZipOutputStream(outputStream);
        try {
            // 添加到zip    
            zip.putNextEntry(new ZipEntry(fileName));
            IOUtils.write(coments, zip, "UTF-8");
            IOUtils.closeQuietly();
            zip.closeEntry();
        } catch (IOException e) {
            throw new RRException("生成压缩文件失败" , e);
        }
        IOUtils.closeQuietly(zip);
        return outputStream.toByteArray();
	}

}
